﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using TaxBusiness.Model;

namespace TaxBusiness.DAL
{
    public class BusinessDB
    {
        private string _insert = "INSERT INTO [tb_Business]" +
                          " ([BusinessCode]" +
                          " ,[CustomerID]" +
                          " ,[BusinessType]" +
                          " ,[Charges]" +
                          " ,[BeginDate]" +
                          " ,[EndDate]" +
                          " ,[Contract]" +
                          " ,[Introducer]" +
                          " ,[Agent]" +
                          " ,[Items]" +
                          " ,[TheOffice]" +
                          " ,[TheDepartment]" +
                          " ,[OwingMoney]" +
                          " ,[FillIn]" +
                          " ,[FillInID]" +
                          " ,[FillInDate]" +
                          " ,[Payment])" +
                     " VALUES" +
                          " (@BusinessCode," +
                          "@CustomerID," +
                          "@BusinessType," +
                          "@Charges," +
                          "@BeginDate," +
                          "@EndDate," +
                          "@Contract," +
                          "@Introducer," +
                          "@Agent," +
                          "@Items," +
                          "@TheOffice," +
                          "@TheDepartment," +
                          "@OwingMoney," +
                          "@FillIn," +
                          "@FillInID," +
                          "@FillInDate," +
                          "@Payment)";

        //private string _select = "SELECT * FROM [tb_Business] where ID=@ID";

        private string _select = "SELECT * FROM [VBusiness] where ID=@ID";

        //private string _selectCharging = "SELECT * FROM ChargingData WHERE BusinessID=@BusinessID ORDER BY ID DESC";
        private string _selectCharging = "SELECT ChargingMoney,PayMethod,ChargingDate FROM tb_Business WHERE ID=@BusinessID";

        private string _updateCheck = "UPDATE [tb_Business]" +
                           " SET [OwingMoney] = [OwingMoney] + @ChargingMoney" +
                           ",[BillDate] = @BillDate" +
                           ",[BillDepartment] = @BillDepartment" +
                           ",[Verifier] = @Verifier" +
                           ",[VerifyDate]=@VerifyDate" +
                           ",[BillMoney]=@BillMoney" +
                           " WHERE ID=@ID";

        private string _updateBill = "UPDATE [tb_Business]" +
                           " SET [BillDate] = @BillDate" +
                           ",[BillDepartment] = @BillDepartment" +
                           ",[BillMoney]=@BillMoney" +
                           ",[ChargingMoney]=@ChargingMoney" +
                           ",[ChargingDate]=@ChargingDate" +
                           ",[PayMethod]=@PayMethod" +
                           " WHERE ID=@BusinessID";

        private string _updateCharing = "UPDATE [tb_Business]" +
                           " SET [OwingMoney] = [OwingMoney] + @ChargingMoney" +
                           " WHERE ID=@BusinessID";

        private string _update = "UPDATE [tb_Business]" +
                           "SET [BusinessCode] = @BusinessCode" +
                            "  ,[BusinessType] = @BusinessType" +
                            //"  ,[TypeName] = @TypeName" +
                            "  ,[Charges] = @Charges" +
                            "  ,[BeginDate] = @BeginDate" +
                            "  ,[EndDate] = @EndDate" +
                            "  ,[Contract] = @Contract" +
                            "  ,[Introducer] = @Introducer" +
                            "  ,[Agent] = @Agent" +
                            "  ,[Items] = @Items" +
                            "  ,[TheOffice] = @TheOffice" +
                            "  ,[TheDepartment] = @TheDepartment" +
                            "  ,[Payment] = @Payment" +
                            " ,[OwingMoney]=[ChargingMoney]-@Charges" +
                         " WHERE ID=@ID";

        private string _updatePaidup = "UPDATE [tb_Business] SET [Paidup]=1 WHERE ID=@ID";

        private string _delete = "DELETE FROM [tb_Business] WHERE [ID] in @ID";

        private string _updateOverTime = "UPDATE [tb_Business] set [currentState]=@result where ID IN @ID";

        private string _insertChargingDate = "UPDATE [tb_Business] " +
                                     "SET [ChargingMoney]=@ChargingMoney" +
                                     ",[PayMethod]=@PayMethod" +
                                     ",[ChargingDate]=@ChargingDate" +
                                     ",[InputChargingDate]=@InputDate" +
                                     " WHERE ID=@BusinessID";

        private string _tableName = "[tb_Business]";

        private string _vTableName = "[VBusiness]";

        private string _fielsAll = "*";

        private string _primaryKey = "[ID]";

        private string _selectAll = "select {0} from {1} where {2} order by {3} {4}";

        private string _count = "select count(*) from {0} where {1}";

        private string _selectPagenation = "select {0} from (select (row_number() over(order by {1} {6})) AS rownum,* from {2} where {3}) as tempdb where rownum between '{4}' and '{5}'";

        private string _selectPayment = "SELECT [ID],[Payment] FROM [PaymentType] order by ID";

        private string _countBillByUnit = "select billunitname,count(id) as BillCount ,isnull(sum(billmoney),0) as BillMoney from vbusiness where billdate>=@Date1 and billdate<=@Date2 group by billunitname";

        private string _countBillByOffice = "select officename,sum(case when billdate is not null then billmoney else 0 end) AS [MoneyWithBill],sum(case when billdate is null then charges else 0 end) as [MoneyWithoutBill] from vbusiness where fillindate>=@Date1 and fillindate<=@Date2 group by officename";

        public int Save(Model.Business business)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_insert, business);
            }
        }

        public int Update(Model.Business business)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_update, business);
            }
        }

        public int UpdateCheck(Model.Charging charging)
        {
            int i = 0;
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                cn.Open();
                IDbTransaction transacation = cn.BeginTransaction();
                try
                {
                    i = cn.Execute(_updateCheck, charging, transacation);
                    cn.Execute(_insertChargingDate, charging, transacation);
                    transacation.Commit();
                }
                catch (Exception e)
                {
                    transacation.Rollback();
                    throw new Exception(e.Message);
                }
            }
            return i;
        }

        public int UpdateBill(Model.Charging charging)
        {
            int i = 0;
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                try
                {
                    i = cn.Execute(_updateBill, charging);
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
            }
            return i;
        }

        public int UpdateCharging(Model.ChargingData charging)
        {
            int i = 0;
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                cn.Open();
                IDbTransaction transacation = cn.BeginTransaction();
                try
                {
                    i = cn.Execute(_updateCharing, charging, transacation);
                    cn.Execute(_insertChargingDate, charging, transacation);
                    transacation.Commit();
                }
                catch (Exception e)
                {
                    transacation.Rollback();
                    throw new Exception(e.Message);
                }
            }
            return i;
        }

        public int UpdatePaidup(int id)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_updatePaidup, new { ID = id });
            }
        }

        public int UpdateOverTime(int[] id, string result)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_updateOverTime, new { ID = id, result=result });
            }
        }
        public int Delete(int[] id)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_delete, new { ID = id });
            }
        }

        public Business Get(int id)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Query<Business>(_select, new { ID = id }).First();
            }
        }

        public List<Model.Business> GetAll(int pageSize, int pageIndex, string whereStr, string order, string keyWord)
        {
            List<Model.Business> businessList = new List<Business>();
            int pageMax = pageIndex * pageSize;
            int pageMin = pageMax - pageSize + 1;
            string sql;
            if (pageIndex > 0)
                sql = string.Format(_selectPagenation, _fielsAll, keyWord, _vTableName, whereStr, pageMin, pageMax, order);
            else
                sql = string.Format(_selectAll, _fielsAll, _vTableName, whereStr, keyWord, order);
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                businessList = cn.Query<Model.Business>(sql).ToList();
            }
            return businessList;
        }

        public int GetCount(string whereStr)
        {
            string sql = string.Format(_count, _vTableName, whereStr);
            int recordCount = 0;
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                recordCount = cn.Query<int>(sql).First();
            }
            return recordCount;
        }

        public List<ChargingData> GetChargingData(int businessid)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Query<ChargingData>(_selectCharging, new { BusinessID = businessid }).ToList();
            }
        }

        public List<PaymentType> GetPayment()
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Query<PaymentType>(_selectPayment).ToList();
            }
        }

        public List<CountBill> CountBill(string date1, string date2)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Query<CountBill>(_countBillByUnit, new { Date1 = date1, Date2 = date2 }).ToList();
            }
        }

        public List<CountBillbyOffice> CountBillByOffice(string date1, string date2)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Query<CountBillbyOffice>(_countBillByOffice, new { Date1 = date1, Date2 = date2 }).ToList();
            }
        }

        public List<CountResult> CountBusiness(string countType, string date1, string date2)
        {
            string sql = "";
            CountCondition countsql = new CountCondition();
            if (countType == "1")
                sql = countsql.CountByOffice;
            if (countType == "2")
                sql = countsql.CountByDepartment;
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Query<CountResult>(sql, new { Date1 = date1, Date2 = date2 }).ToList();
            }
        }
    }
}
